create procedure SMSelectByOrgID
(
@OrgID uniqueidentifier,
@PageNumber int,
@PageSize int,
@TotalItems int output,
@IncludeInactive bit
)
as
-- declare a new TABLE variable
declare @SupportingMaterials table
(
RowNumber int,
SupportingMaterialID uniqueidentifier,
URL nvarchar(50),
Description nvarchar(1000),
Type uniqueidentifier,
AddedBy uniqueidentifier,
AddedDate datetime,
IsActive bit
)

-- populate the table variable with the
-- complete list of supporting materials
insert into @SupportingMaterials
select row_number() over (order by (select 0)), SupportingMaterialID, 
	   URL, Description, Type, AddedBy, AddedDate, IsActive
from   SupportingMaterial
where  OrgID = @OrgID and IsActive = @IncludeInactive

-- return the total number of supporting materials using an OUTPUT variable
select @TotalItems = count(SupportingMaterialID) from @SupportingMaterials

-- extract the requested page of products
select SupportingMaterialID, URL, Description, Type, AddedBy, AddedDate, IsActive
from   @SupportingMaterials
where  rownumber > (@PageNumber - 1) * @PageSize and
	   rownumber <= @PageNumber * @PageSize
	   